using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace SharePoint.Security.DocHelpers.ExportFormats
{
    public class ExcelExportFormat : IExportFormat
    {
        public string Export(IEnumerable<KeyValuePair<string, string>> summaryInfo, IEnumerable<RuleData> ruleData)
        {
            string exportFileName = string.Format("Report-{0:yyyy-MM-dd_hh-mm-ss-tt}.xlsx", DateTime.Now);

            string exportFileFullPath = string.Format("{0}\\AuditResults\\{1}", Directory.GetCurrentDirectory(), exportFileName);
            string templateFileFullPath = string.Format("{0}\\ExportFormats\\Templates\\ExcelExportFormat.xlsx", Directory.GetCurrentDirectory());

            if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\AuditResults"))
            {
                Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\AuditResults");
            }

            File.Copy(templateFileFullPath, exportFileFullPath, true);
            CreateExcelFile(exportFileFullPath, summaryInfo, ruleData);
            return exportFileFullPath;
        }

        private void CreateExcelFile(string localPath, IEnumerable<KeyValuePair<string, string>> summaryInfo, IEnumerable<RuleData> ruleData)
        {
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(localPath, true))
            {
                var id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Summary").Id;
                var worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                AddHeaderValues(sheetData, summaryInfo);

                // Adding content to excel for all other settings except 'WebApplication Settings'
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "Results").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                AddResults(sheetData, ruleData.Where(s=> s.IsAppSetting == false));

                // Adding 'WebApplication Settings' content to excel
                id = myWorkbook.WorkbookPart.Workbook.Descendants<Sheet>().First(s => s.Name == "AppSettings").Id;
                worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(id);
                sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                AddResults(sheetData, ruleData.Where(s => s.IsAppSetting == true));
            }
        }

        private void AddResults(OpenXmlCompositeElement sheetData, IEnumerable<RuleData> ruleData)
        {
            var ruleDatas = ruleData as RuleData[] ?? ruleData.ToArray();
            UInt32Value style = 0U;
            int rowIndex = 2;

            foreach (var data in ruleDatas)
            {
                var row = new Row
                              {
                                  RowIndex = (UInt32)rowIndex
                              };

                Cell categoryCell = CreateExcelCell(rowIndex, "A", data.Category, style);
                row.AppendChild(categoryCell);

                Cell descCell = CreateExcelCell(rowIndex, "B", data.Description, style);
                row.AppendChild(descCell);

                Cell statusCell = CreateExcelCell(rowIndex, "C", data.Status, style);
                row.AppendChild(statusCell);

                // check for non-WebApplication Settings
                if (!data.IsAppSetting)
                {
                    Cell moreInfoCell = CreateExcelCell(rowIndex, "D", data.MoreInfo, style);
                    row.AppendChild(moreInfoCell);
                }

                sheetData.AppendChild(row);
                rowIndex = rowIndex + 1;
            }
        }

        private static Cell CreateExcelCell(int rowIndex, string columnConstant, string cellText, UInt32Value style)
        {
            var contentCell = new Cell
                                  {
                                      DataType = CellValues.InlineString,
                                      StyleIndex = style,
                                      CellReference = columnConstant + rowIndex.ToString(CultureInfo.InvariantCulture)
                                  };
            
            var contentCellInlineStr = new InlineString();
            var contentCellTxt = new Text
                                     {
                                         Text = cellText, 
                                         Space = SpaceProcessingModeValues.Preserve
                                     };
            
            contentCellInlineStr.AppendChild(contentCellTxt);
            contentCell.AppendChild(contentCellInlineStr);
            return contentCell;
        }

        private void AddHeaderValues(SheetData sheetData, IEnumerable<KeyValuePair<string, string>> summaryData)
        {
            foreach (var summaryInfo in CreateSummary(summaryData))
            {
                FillStringDataInExistingExcelRow(sheetData, summaryInfo.RowIndex, summaryInfo.ColumnIndex, summaryInfo.Reference, summaryInfo.Value);
            }
        }

        private static void FillStringDataInExistingExcelRow(SheetData sheetData, int rowIndex, int cellIndex, string cellReference, string cellText)
        {
            var r = sheetData.ChildElements[rowIndex] as Row;
            if (r == null) return;
            
            var contentCell = r.ChildElements[cellIndex] as Cell;
            if (contentCell == null)
            {
                return;
            }
            contentCell.DataType = CellValues.InlineString;
            contentCell.CellReference = cellReference;
            var contentCellInlineStr = new InlineString();
            var contentCellTxt = new Text
                                     {
                                         Text = cellText, 
                                         Space = SpaceProcessingModeValues.Preserve
                                     };

            contentCellInlineStr.AppendChild(contentCellTxt);
            contentCell.AppendChild(contentCellInlineStr);
        }

        private IEnumerable<ExcelCellData> CreateSummary(IEnumerable<KeyValuePair<string, string>> summaryData)
        {
            var summaryInfo = new List<ExcelCellData>();
            var rowIndex = 1;
            const int cellIndexForLabel = 0;
            const int cellIndexForValue = 1;
            UInt32Value style = 3U;
            const string cellReferenceForLabel = "A";
            const string cellReferenceForValue = "B";


            foreach (var summary in summaryData)
            {
                var label = summary.Key;
                var value = summary.Value;
                var cellRefLabel = string.Format("{0}{1}", rowIndex + 1, cellReferenceForLabel);
                var cellRefValue = string.Format("{0}{1}", rowIndex + 1, cellReferenceForValue);

                var labelCell = new ExcelCellData(rowIndex, cellIndexForLabel, cellRefLabel, style, label);
                var valueCell = new ExcelCellData(rowIndex, cellIndexForValue, cellRefValue, style, value);
                summaryInfo.Add(labelCell);
                summaryInfo.Add(valueCell);

                rowIndex = rowIndex + 1;
            }

            return summaryInfo;
        }
    }
}